/**
 生成随机字符串
 select investment.random_string(8);
 */
DROP FUNCTION IF EXISTS stock.func_random_string(INTEGER);
CREATE OR REPLACE FUNCTION stock.func_random_string(IN num INTEGER) RETURNS text
AS $$
DECLARE
  return_val TEXT;
BEGIN
  SELECT array_to_string(array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM (ceil(random()*62))::int FOR 1)
                                      INTO return_val FROM generate_series(1, num)), '');
  RETURN return_val;
END
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION stock.func_random_string(INTEGER) IS '生成随机字符串';

/**
	清空数据库
	SELECT stock.func_truncate_database();
 */
DROP FUNCTION IF EXISTS stock.func_truncate_database();
CREATE OR REPLACE FUNCTION stock.func_truncate_database() RETURNS BOOLEAN
AS $$
BEGIN
  DROP TABLE IF EXISTS stock.details_log;
  DROP TABLE IF EXISTS stock.transaction;
  DROP TABLE IF EXISTS stock.position;
  DROP TABLE IF EXISTS stock.trading;
  DROP TABLE IF EXISTS stock.account;
  DROP TABLE IF EXISTS stock.owner;
  DROP TABLE IF EXISTS stock.market;
  DROP TABLE IF EXISTS stock.stock;
  DROP TABLE IF EXISTS stock.dictionary;
  DROP FUNCTION IF EXISTS stock.trigger_insert_before_details_log_function();
  DROP FUNCTION IF EXISTS stock.func_random_string(INTEGER);
  DROP FUNCTION IF EXISTS stock.func_search_dictionary(JSON);
  DROP FUNCTION IF EXISTS stock.func_search_dictionary(VARCHAR, VARCHAR);
  DROP FUNCTION IF EXISTS stock.func_truncate_database();
  DROP SCHEMA IF EXISTS stock;
  RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
  RETURN json_build_object('type', 'Error', 'message', '清空数据库操作失败!', 'error', replace(SQLERRM, '"', '`'), 'sqlstate', SQLSTATE);
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION stock.func_truncate_database() IS '清空数据库';

/**
	查询字典数据
	SELECT stock.func_search_dictionary(json_build_object('pkid', '1'));
	SELECT stock.func_search_dictionary(json_build_object('code', 'stock'));
	SELECT stock.func_search_dictionary(json_build_object('kind', 'stock.type'));
	SELECT stock.func_search_dictionary(json_build_object('upid', '1'));
	SELECT stock.func_search_dictionary(json_build_object('regexp', '600009'));
 */
DROP FUNCTION IF EXISTS stock.func_search_dictionary(JSON);
CREATE OR REPLACE FUNCTION stock.func_search_dictionary(IN dict JSON) RETURNS JSON
AS $$
DECLARE
  dictionarys JSON DEFAULT NULL;
BEGIN
  IF (json_extract_path_text(dict, 'pkid') IS NOT NULL) THEN
    SELECT row_to_json(diction) INTO dictionarys FROM stock.dictionary diction WHERE pkid = json_extract_path_text(dict, 'pkid')::INTEGER;
  ELSEIF (json_extract_path_text(dict, 'code') IS NOT NULL) THEN
    SELECT row_to_json(diction) INTO dictionarys FROM stock.dictionary diction WHERE code = json_extract_path_text(dict, 'code');
  ELSEIF (json_extract_path_text(dict, 'kind') IS NOT NULL) THEN
    SELECT json_agg(diction) INTO dictionarys FROM stock.dictionary diction WHERE kind = json_extract_path_text(dict, 'kind');
  ELSEIF (json_extract_path_text(dict, 'upid') IS NOT NULL) THEN
    SELECT json_agg(diction) INTO dictionarys FROM stock.dictionary diction WHERE upid = json_extract_path_text(dict, 'upid')::INTEGER;
  ELSEIF (json_extract_path_text(dict, 'regexp') IS NOT NULL) THEN
    SELECT row_to_json(diction) INTO dictionarys FROM stock.dictionary diction WHERE json_extract_path_text(dict, 'regexp') ~ regexp LIMIT 1;
  ELSE
    RETURN json_build_object('type', 'Error', 'message', '查询字典数据操作失败!', 'error', '给定的参数不合法', 'sqlstate', 230);
  END IF;
  IF(dictionarys IS NOT NULL) THEN
    RETURN json_build_object('type', 'Success', 'message', '查询字典数据作操作成功!', 'code', 200, 'data', dictionarys);
  ELSE
    RETURN json_build_object('type', 'Error', 'message', '查询字典数据操作失败!', 'error', '没有找到指定的数据', 'sqlstate', 230);
  END IF;
EXCEPTION WHEN OTHERS THEN
  RETURN json_build_object('type', 'Error', 'message', '用户校验操作失败!', 'error', replace(SQLERRM, '"', '`'), 'sqlstate', SQLSTATE);
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION stock.func_search_dictionary(JSON) IS '查询字典数据';

/**
  查询字典数据
	SELECT stock.func_search_dictionary('pkid', '1');
	SELECT stock.func_search_dictionary('code', 'stock');
	SELECT stock.func_search_dictionary('kind', 'stock.type');
	SELECT stock.func_search_dictionary('upid', '1');
	SELECT stock.func_search_dictionary('regexp', '600009');
 */
DROP FUNCTION IF EXISTS stock.func_search_dictionary(VARCHAR, VARCHAR);
CREATE OR REPLACE FUNCTION stock.func_search_dictionary(IN dict VARCHAR, IN vals VARCHAR) RETURNS JSON
AS $$
DECLARE
  dictionarys JSON DEFAULT NULL;
BEGIN
  SELECT stock.func_search_dictionary(json_build_object(dict, vals)) INTO dictionarys;
  RETURN dictionarys;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION stock.func_search_dictionary(VARCHAR, VARCHAR) IS '查询字典数据';

CREATE OR REPLACE FUNCTION stock.trigger_insert_before_details_log_function()
  RETURNS TRIGGER AS $$
DECLARE
  accountJson JSON;                 -- 当前账户基本数据
  targetJSON JSON;                  -- 目标账户基本数据
  stockJson JSON;
  commissionValue DECIMAL(9, 2);    -- 佣金
  stampDutyValue DECIMAL(9, 2);     -- 印花税
  transferFeeValue DECIMAL(9, 2);   -- 过户费
  stockNum INTEGER DEFAULT 0;
  positionNum INTEGER DEFAULT 0;
  numberValue INTEGER DEFAULT 0;
BEGIN
  -- 获取当前账户基本数据
  SELECT row_to_json(acc) INTO accountJson FROM stock.account acc WHERE acc.uuid = NEW.account_id;
  NEW.owner_id := accountJson->>'owner_id';
  IF(NEW.manipulate = 'account.stock') THEN
    -- 场内证券交易
    -- 获取当前证券基本数据
    SELECT row_to_json(sto) INTO stockJson FROM stock.stock sto WHERE sto.stock_code = NEW.stock_code;
    IF(stockJson->>'stock_securities' = 'securities.stock') THEN
      -- 场内股票交易
      IF((NEW.number * NEW.price * CAST(accountJson->>'stock_rates' AS DECIMAL)) < 5) THEN
        -- 场内股票交易手续费是否小于5元
        IF(accountJson->>'stock_is_free5')THEN
          -- 计算免5
          commissionValue := NEW.number * NEW.price * CAST(accountJson->>'stock_rates' AS DECIMAL);
        ELSE
          commissionValue := 5;
        END IF;
      ELSE
        commissionValue := NEW.number * NEW.price * CAST(accountJson->>'stock_rates' AS DECIMAL);
      END IF;
      -- 计算股票印花税
      IF(NEW.operate = 'purchase') THEN
        stampDutyValue := 0;
      ELSE
        stampDutyValue := NEW.number * NEW.price * 0.001;
      END IF;
      -- 计算上证过户费
      IF(NEW.stock_market = 'a.sh.stock') THEN
        -- 判断是否卖出，计算上证过户费
        transferFeeValue := NEW.number * NEW.price * 0.00002;
      ELSE
        transferFeeValue := 0;
      END IF;
    ELSE
      -- 场内基金、债券交易
      IF(NEW.number * NEW.price < 10000.00) THEN
        -- 是否小于10000
        IF(accountJson->>'fund_is_free5')THEN
          -- 佣金免5
          commissionValue := NEW.number * NEW.price * CAST(accountJson->>'fund_rates' AS DECIMAL);
        ELSE
          commissionValue := 5;
        END IF;
      ELSE
        commissionValue := NEW.number * NEW.price * CAST(accountJson->>'fund_rates' AS DECIMAL);
      END IF;
      stampDutyValue := 0;
      transferFeeValue := 0;
    END IF;
    SELECT count(1), sum(number) INTO stockNum, positionNum FROM stock.position WHERE account_id = NEW.account_id AND stock_code = NEW.stock_code;
    IF (NEW.operate = 'purchase') THEN
      -- 买入
      NEW.amount := NEW.number * NEW.price + commissionValue + stampDutyValue + transferFeeValue;
      NEW.current_balance = CAST(accountJson->>'balance' AS DECIMAL) - NEW.amount;
      NEW.abstracts := CONCAT('【买入】证券名称：', stockJson->'stock_label', '，单价：￥', NEW.price, '，数量：', NEW.number, '，发生金额：￥', NEW.amount, '，证券账户：', accountJson->>'account_label', '，账户余额：￥', NEW.current_balance);
      IF (stockNum = 0 OR positionNum = 0) THEN
        -- 建仓
        INSERT INTO stock.position(owner_id, account_id, stock_code, marketplace, capitalized, number, full_space, buy_pitch, sale_pitch)
        VALUES (NEW.owner_id, NEW.account_id, NEW.stock_code, 'stock', NEW.amount/NEW.number, NEW.number, 100000, 0.01, 0.01);
      ELSE
        UPDATE stock.position p SET capitalized = (p.capitalized * p.number + NEW.amount)/(p.number + NEW.number), number = (p.number + NEW.number)
        WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
      END IF;
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
    ELSEIF (NEW.operate = 'sell_out') THEN
      -- 卖出
      SELECT p.number INTO numberValue FROM stock.position p WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
      IF((numberValue < ABS(NEW.number))) THEN
        RETURN json_build_object('type', 'Error', 'message', '计算持仓成本统计计算失败!', 'error', '持仓数量小于卖出数量！');
      END IF;
      NEW.amount := NEW.number * NEW.price - commissionValue - stampDutyValue - transferFeeValue;
      NEW.current_balance = CAST(accountJson->>'balance' AS DECIMAL) + NEW.amount;
      NEW.abstracts := CONCAT('【卖出】证券名称：', stockJson->'stock_label', '，单价：￥', NEW.price, '，数量：', NEW.number, '，发生金额：￥', NEW.amount, '，证券账户：', accountJson->>'account_label', '，账户余额：￥', NEW.current_balance);
      IF(stockNum > 0 AND positionNum > 0) THEN
        IF((numberValue - NEW.number) = 0) THEN
          UPDATE stock.position p SET capitalized = 0, number = 0 WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
        ELSE
          UPDATE stock.position p SET capitalized = (p.capitalized * p.number - NEW.amount)/(p.number - NEW.number), number = (p.number - NEW.number)
          WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
        END IF;
      ELSE
        RETURN json_build_object('type', 'Error', 'message', '计算持仓成本统计计算失败!', 'error', '不能空卖！');
      END IF;
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
    ELSEIF (NEW.operate = 'stock_dividend') THEN
      -- 股息
      NEW.current_balance = CAST(accountJson->>'balance' AS DECIMAL) + NEW.amount;
      NEW.abstracts := CONCAT('【股息】股票名称：', stockJson->'stock_label', '，发生金额：￥', NEW.amount, '，证券账户：', accountJson->>'account_label', '，账户余额：￥', NEW.current_balance);
      UPDATE stock.position p SET capitalized = (p.capitalized * p.number - NEW.amount )/p.number WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
    ELSEIF (NEW.operate = 'share_allotment') THEN
      -- 派股
      NEW.abstracts := CONCAT('【派股】股票名称：', stockJson->'stock_label', '，发生数量：', NEW.number, '，证券账户：', accountJson->>'account_label');
      UPDATE stock.position p SET capitalized = (p.capitalized * p.number)/(p.number + NEW.number), number = (p.number + NEW.number)
      WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
    ELSEIF (NEW.operate = 'bond_dividend') THEN
      -- 国债收益
      NEW.current_balance = CAST(accountJson->>'balance' AS DECIMAL) + NEW.amount;
      NEW.abstracts := CONCAT('【国债收益】证券账户：', accountJson->>'account_label', '，发生金额：￥', NEW.amount, '，账户余额：￥', NEW.current_balance);
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
    ELSE
      -- 其他
    END IF;
    IF (NEW.operate != 'bond_dividend') THEN
      INSERT INTO stock.trading (account_id, stock_code, operate, methods, price, number, amount, capitalized, positions, commission, stamp_duty, transfer_fee)
      SELECT NEW.account_id, NEW.stock_code, NEW.operate, NEW.methods, NEW.price, NEW.number, NEW.amount, po.capitalized, po.number, commissionValue, stampDutyValue, transferFeeValue
      FROM stock.position po WHERE po.account_id = NEW.account_id AND po.stock_code = NEW.stock_code;
    END IF;
  ELSEIF(NEW.manipulate = 'account.fund') THEN
    -- 场外基金交易
    SELECT count(1) INTO stockNum FROM stock.position p WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
    IF(NEW.operate = 'apply_purchase') THEN
      -- 申购
      IF(stockNum = 0) THEN
        INSERT INTO stock.position (owner_id, account_id, stock_code, marketplace, capitalized, number)
        VALUES (NEW.owner_id, NEW.account_id, NEW.stock_code, NEW.manipulate, NEW.amount/NEW.number, NEW.number);
      ELSE
        UPDATE stock.position p SET p.capitalized = (p.capitalized * p.number + NEW.amount) / (p.number + NEW.number),
                                    p.number = (p.number + NEW.number)
        WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
      END IF;
    ELSEIF(NEW.operate = 'fund_dividend') THEN
      -- 红利再投资
      UPDATE stock.position p SET p.capitalized = (p.capitalized * p.number) / (p.number + NEW.number),
                                  p.number = (p.number + NEW.number)
      WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
    ELSEIF(NEW.operate = 'redeem') THEN
      -- 赎回
      UPDATE stock.position p SET p.capitalized = CASE (p.number - NEW.number) WHEN 0 THEN 0 ELSE (p.capitalized * p.number + NEW.amount) / (p.number - NEW.number) END,
                                  p.number = (p.number - NEW.number)
      WHERE p.account_id = NEW.account_id AND p.stock_code = NEW.stock_code;
    ELSE
      -- 其他
    END IF;
    INSERT INTO stock.transaction (account_id, stock_code, operate, amount, affirm_share, affire_price, expenses, affire_date)
    VALUES (NEW.account_id, NEW.stock_code, NEW.operate, NEW.amount, NEW.number, CASE NEW.amount WHEN NULL THEN 0 ELSE NEW.price END,
            CASE NEW.amount WHEN NULL THEN 0 ELSE (NEW.amount - NEW.price * NEW.number) END, NEW.datetime);
  ELSEIF(NEW.manipulate = 'account.bank') THEN
    -- 银行账户操作
    IF(NEW.operate = 'bank.to_stock') THEN
      -- 银转证操作
      IF(NEW.target_id IS NULL) THEN
        RETURN json_build_object('type', 'Error', 'message', '银行账户资金操作失败!', 'error', '未指定银转证操作证券账户', 'sqlstate', 230);
      END IF;
      SELECT row_to_json(tar) INTO targetJSON FROM stock.account tar WHERE tar.uuid = NEW.target_id;
      NEW.current_balance := CAST(accountJson->>'balance' AS DECIMAL) - NEW.amount;
      NEW.target_balance := CAST(targetJSON->>'balance' AS DECIMAL) + NEW.amount;
      NEW.abstracts := CONCAT('【银转证】目标证券账户：', targetJSON->>'account_label', '，发生金额：￥', NEW.amount, '，证券账户余额：￥', NEW.target_balance, '，银行账户：', accountJson->>'account_label', '，银行账户余额：￥', NEW.current_balance);
      -- 变更当前账户资金余额
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
      -- 变更目标账户资金余额
      UPDATE stock.account SET balance = NEW.target_balance WHERE uuid = NEW.target_id;
    ELSEIF(NEW.operate = 'bank.to_bank') THEN
      -- 证转银操作
      IF(NEW.target_id IS NULL) THEN
        RETURN json_build_object('type', 'Error', 'message', '银行账户资金操作失败!', 'error', '未指定证转银操作证券账户', 'sqlstate', 230);
      END IF;
      SELECT row_to_json(tar) INTO targetJSON FROM stock.account tar WHERE tar.uuid = NEW.target_id;
      NEW.current_balance := CAST(accountJson->>'balance' AS DECIMAL) + NEW.amount;
      NEW.target_balance := CAST(targetJSON->>'balance' AS DECIMAL) - NEW.amount;
      NEW.abstracts := CONCAT('【证转银】目标银行账户：', accountJson->>'account_label', '，发生金额：￥', NEW.amount, '，银行账户余额：￥', NEW.current_balance, '，证券账户：', targetJSON->>'account_label', '，证券账户余额：￥', NEW.target_balance);
      -- 变更当前账户资金余额
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
      -- 变更目标账户资金余额
      UPDATE stock.account SET balance = NEW.target_balance WHERE uuid = NEW.target_id;
    ELSEIF(NEW.operate = 'bank.dividend') THEN
      -- 利息操作
      NEW.current_balance := CAST(accountJson->>'balance' AS DECIMAL) + NEW.amount;
      NEW.abstracts := CONCAT('【利息结算】银行账户：', accountJson->>'account_label', '，发生金额：￥', NEW.amount, '，账户余额：￥', NEW.current_balance);
      -- 变更当前账户资金余额
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
    ELSEIF(NEW.operate = 'bank.money_enter') THEN
      -- 转入操作
      NEW.current_balance := CAST(accountJson->>'balance' AS DECIMAL) + NEW.amount;
      NEW.abstracts := CONCAT('【资金转入】银行账户：', accountJson->>'account_label', '，发生金额：￥', NEW.amount, '，账户余额：￥', NEW.current_balance);
      -- 变更当前账户资金余额
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
    ELSEIF(NEW.operate = 'bank.money_out') THEN
      -- 转出操作
      NEW.current_balance := CAST(accountJson->>'balance' AS DECIMAL) - NEW.amount;
      NEW.abstracts := CONCAT('【资金转出】银行账户：', accountJson->>'account_label', '，发生金额：￥', NEW.amount, '，账户余额：￥', NEW.current_balance);
      -- 变更当前账户资金余额
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
    ELSE
      -- 其他银行账户操作
    END IF;
  ELSEIF(NEW.manipulate = 'account.loan') THEN
    -- 消费信贷账户操作
    IF(NEW.operate = 'consume.bank_loan') THEN
      -- 消费贷款
      NEW.current_balance := CAST(accountJson->>'balance' AS DECIMAL) + NEW.amount;
      NEW.abstracts = CONCAT('【消费贷款】信贷账户：', accountJson->>'account_label', '借款金额：￥', NEW.amount, '，账户余额：￥', NEW.current_balance);
      -- 变更当前贷款账户资金余额
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
    ELSEIF(NEW.operate = 'consume.bank_pay') THEN
      -- 贷款偿付
      IF(NEW.target_id IS NULL) THEN
        RETURN json_build_object('type', 'Error', 'message', '贷款偿付资金操作失败!', 'error', '未指定贷款偿付操作银行账户', 'sqlstate', 230);
      END IF;
      SELECT row_to_json(tar) INTO targetJSON FROM stock.account tar WHERE tar.uuid = NEW.target_id;
      NEW.current_balance := CAST(accountJson->>'balance' AS DECIMAL) - NEW.amount;
      NEW.target_balance := CAST(targetJSON->>'balance' AS DECIMAL) - NEW.amount;
      NEW.abstracts = CONCAT('【贷款偿付】还款银行账户：', targetJSON->>'account_label', '，还款金额：￥', NEW.amount, '，银行账户余额：￥', NEW.target_balance, '，信贷账户：', accountJson->>'account_label', '，信贷账户余额：￥', NEW.current_balance);
      -- 变更当前贷款账户资金余额
      UPDATE stock.account SET balance = NEW.current_balance WHERE uuid = NEW.account_id;
      -- 变更当前银行账户资金余额
      UPDATE stock.account SET balance = NEW.target_balance WHERE uuid = NEW.target_id;
    ELSE
      -- 其他消费信贷账户操作
    END IF;
  ELSE
    -- 其他操作
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION stock.trigger_insert_before_details_log_function() IS '【stock.details_log】表的触发器函数';

CREATE TRIGGER trigger_insert_before_details_log BEFORE INSERT
  ON stock.details_log FOR EACH ROW EXECUTE PROCEDURE stock.trigger_insert_before_details_log_function();
COMMENT ON TRIGGER trigger_insert_before_details_log ON stock.details_log IS '【stock.details_log】表的触发器';